package com.sig.util;

import java.lang.reflect.Field;
import java.sql.*;
import java.util.*;
import java.util.concurrent.atomic.AtomicInteger;

/**
 * @author sig
 * @since 2016-6-6 09:12:19
 */
public class JdbcUtil {
    private static final String DRIVER = "com.mysql.jdbc.Driver";

    private static Connection conn = null;
    private static PreparedStatement ps = null;
    private static ResultSet rs = null;

    private String db = "test_base";
    private String user = "root";
    private String password = "";

    public void getConn(String db, String user, String password) throws ClassNotFoundException, SQLException {
        this.db = db;
        this.user = user;
        this.password = password;
        String url = "jdbc:mysql://127.0.0.1:3306/" + this.db + "?useUnicode=true&amp;characterEncoding=UTF-8";
        Class.forName(DRIVER);
        conn = DriverManager.getConnection(url, this.user, this.password);
    }

    private static synchronized boolean update(String sql, List<Object> params) throws SQLException {
        ps = conn.prepareStatement(sql);
        int index = 1;

        if (params != null && !params.isEmpty()) {

            for (Object param : params) {
                ps.setObject(index++, param);
            }
        }
        return ps.executeUpdate() > 0;
    }

    private static Map<String, Object> query(String sql, List<Object> params) throws SQLException {
        Map<String, Object> map = new HashMap<>();
        ps = conn.prepareStatement(sql);
        int index = 1;

        if (params != null && !params.isEmpty()) {
            for (Object param : params) {
                ps.setObject(index++, param);
            }
        }
        rs = ps.executeQuery();
        ResultSetMetaData data = rs.getMetaData();
        int count = data.getColumnCount();

        while (rs.next()) {

            for (int i = 0; i < count; i++) {
                String name = data.getColumnName(i + 1);
                Object value = rs.getObject(name);

                if (value == null) {
                    value = "";
                }
                map.put(name, value);
            }
        }
        return map;
    }

    private static <T> T query(String sql, List<Object> params, Class<T> cls) throws Exception {
        T t = null;
        int index = 1;
        ps = conn.prepareStatement(sql);

        if (params != null && !params.isEmpty()) {

            for (Object param : params) {
                ps.setObject(index++, param);
            }
        }
        rs = ps.executeQuery();
        ResultSetMetaData data = rs.getMetaData();
        int count = data.getColumnCount();

        while (rs.next()) {
            t = cls.newInstance();

            for (int i = 0; i < count; i++) {
                String name = data.getColumnName(i + 1);
                Object value = rs.getObject(name);

                if (value == null) {
                    value = "";
                }
                Field field = cls.getDeclaredField(name);
                field.setAccessible(true);
                field.set(t, value);
            }
        }
        return t;
    }

    private static List<Map<String, Object>> queryAll(String sql, List<Object> params) throws SQLException {
        List<Map<String, Object>> list = new ArrayList<>();
        ps = conn.prepareStatement(sql);
        int index = 1;

        if (params != null && !params.isEmpty()) {
            for (Object param : params) {
                ps.setObject(index++, param);
            }
        }
        rs = ps.executeQuery();
        ResultSetMetaData data = rs.getMetaData();
        int count = data.getColumnCount();

        while (rs.next()) {
            Map<String, Object> map = new HashMap<>();

            for (int i = 0; i < count; i++) {
                String name = data.getColumnName(i + 1);
                Object value = rs.getObject(name);

                if (value == null) {
                    value = "";
                }
                map.put(name, value);
            }
            list.add(map);
        }
        return list;
    }

    private static <T> List<T> queryAll(String sql, List<Object> params, Class<T> cls) throws Exception {
        List<T> list = new ArrayList<>();
        ps = conn.prepareStatement(sql);
        int index = 1;

        if (params != null && !params.isEmpty()) {

            for (Object param : params) {
                ps.setObject(index++, param);
            }
        }
        rs = ps.executeQuery();
        ResultSetMetaData data = rs.getMetaData();
        int count = data.getColumnCount();

        while (rs.next()) {
            T t = cls.newInstance();

            for (int i = 0; i < count; i++) {
                String name = data.getColumnName(i + 1);
                Object value = rs.getObject(name);

                if (value == null) {
                    value = "";
                }
                Field field = cls.getDeclaredField(name);
                field.setAccessible(true);
                field.set(t, value);
            }
            list.add(t);
        }
        return list;
    }

    public static void releaseConn() {
        if (rs != null) {

            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if (ps != null) {

            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * @param num       数量
     * @param prefixNum 前缀个数
     * @param batch     批次
     * @param suffix    尾数
     * @param name      礼包名称
     * @return
     * @throws SQLException
     */
    public boolean insert(int num, int prefixNum, int batch, int suffix, String name) throws SQLException {
        Set<Long> keyIds = CdKeyUtil.generate(num, prefixNum, batch, suffix);
        boolean flag = false;

        for (long keyId : keyIds) {
            String sql = "INSERT INTO `basecdkeyexchange`" +
                    " (`id`, `keyId`, `name`, `suffix`, `batch`) VALUES ('" +
                    createId() + "', '" + keyId + "', '" + name +
                    "', '" + suffix + "', '" + batch + "')";
            System.out.println(sql);
            flag = update(sql, null);
        }
        return flag;
    }

    private static AtomicInteger id;

    private static int createId() throws SQLException {
        if (null == id) {
            List<Object> params = new ArrayList<>();
            String sql = "SELECT MAX(`id`) AS `id` FROM basecdkeyexchange";
            params.add("id");
            Map<String, Object> map = query(sql, params);
            Integer maxId = (Integer) map.get("id");

            if (null == maxId) {
                id = new AtomicInteger(0);
            } else {
                id = new AtomicInteger(maxId);
            }
        }
        return id.incrementAndGet();
    }
}
